--POUR CONTROLE



define erp='erp_mli2.';
define outils='outils_mli2.';

DROP TABLE se_ach_tariflig;
CREATE TABLE se_ach_tariflig AS (SELECT l.*,CAST (NULL AS VARCHAR(30)) clergp FROM &erp.ach_tariflig l WHERE 1=2);


PROMPT CREATE INDEX se_ach_tariflig_01
CREATE INDEX se_ach_tariflig_01
  ON se_ach_tariflig (
    soc_cod,
    ets_cod,
    fournisseur_cod,
    combtaf_id
  )
  NOLOGGING
;




DROP TABLE temp_ach_tariflig;
CREATE TABLE temp_ach_tariflig  AS (
 SELECT dd.id,c.art,c.four,
       dd.RANGMULTI2 rang,
       To_Date('31/12/1967','dd/mm/yyyy')+dd.DATAP datap,
       CAST (Nvl(Decode(psfa.PXB_FORFSFA,0,NULL,psfa.PXB_FORFSFA),p.PXTR) AS NUMBER(14,4)) PXTR ,
       CAST (t.tranche AS NUMBER) tranche,
       CAST (Decode(df.datfin,NULL,NULL,To_Date('31/12/1967','dd/mm/yyyy')+df.datfin) AS DATE) datfin,
       r.remise,
       tr.txremise
 FROM  uv_pfournisseurs f,
       uv_pcatalogues c,
       UV_PCATALOGUES_DATAP dd,
       UV_PCATALOGUES_DATFIN df,
       UV_PCATALOGUES_TRANCHE t,
       UV_PCATALOGUES_PXTR p,
       UV_PCATALOGUES_PXB_FORFSFA psfa,
       UV_PCATALOGUES_REMISE r,
       UV_PCATALOGUES_TXREMISE tr
 WHERE f.id=c.four
   AND dd.id=c.id
   and p.id(+)=dd.id AND p.RANGMULTI2(+)=dd.RANGMULTI2
   AND psfa.id(+)=dd.id AND psfa.RANGMULTI2(+)=dd.RANGMULTI2
   AND t.id=dd.id AND t.RANGMULTI2=dd.RANGMULTI2
   AND df.id(+)=dd.id AND df.RANGMULTI2(+)=dd.RANGMULTI2
   AND r.id(+)=dd.id AND r.RANGMULTI2(+)=dd.RANGMULTI2
   AND tr.id(+)=dd.id AND tr.RANGMULTI2(+)=dd.RANGMULTI2  ) ;



 INSERT INTO se_ach_tariflig
 (SELECT '01' soc_cod,
         '01' ets_cod,
         a.four fournisseur_cod,
         Nvl((SELECT combtaf_id FROM se_ach_tarifent e WHERE a.art=e.rubrique_1_lib AND a.four=e.fournisseur_cod),0)  combtaf_id,
         0 enreg_id,
         a.remise remise_tx,
         NULL affaire_cod,
         a.rang+1 tariflig_nolig,
         Nvl(datap,To_Date('31/12/1967','dd/mm/yyyy')) debvalid_dat,
         datfin finvalid_dat,
         LAG(tranche+1, 1, 0) OVER (PARTITION BY art,four,datap ORDER BY art,four,datap desc,tranche) tariflig_qt,
         'V' statut_typ,
         pxtr/100 brut_mt,
         pxtr/100 net_mt,
         'N' enreg_s,
         NULL reffour_cod,
         'N' tariflot_ok,
         a.art clergp
    FROM temp_ach_tariflig a ,uv_pproduits_statut_actuel s
    WHERE s.id=a.art AND (s.statut<900 OR s.statut IS NULL)
      --AND art ='020227212790_00044343'
      AND a.pxtr IS NOT null
      ) ;

  DELETE FROM se_ach_tariflig WHERE combtaf_id =0;



  UPDATE se_ach_tariflig tt SET ENREG_ID = (SELECT enreg_id FROM &erp.ach_tariflig t
                                            WHERE tt.soc_cod=t.soc_cod AND tt.ets_cod=t.ets_cod
                                              AND tt.combtaf_id=t.combtaf_id AND tt.fournisseur_cod=t.fournisseur_cod
                                              AND tt.TARIFLIG_NOLIG=t.TARIFLIG_NOLIG)
                            WHERE enreg_id=0 ;


  UPDATE se_ach_tariflig tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL;




 CALL uvse_synchroverserp('soc_cod@ets_cod@fournisseur_cod@combtaf_id@tariflig_nolig','ACH_TARIFLIG','N') ;

 commit;


/*suppresion tarif au rgp des SELF car tarif au generique*/
DELETE erp_mli2.ach_tariflig ll WHERE exists
(SELECT c.gene_cod
FROM &erp.art_comp c, &erp.vrt_combtaf v
WHERE class1_cod='SELF'
  AND v.gene_cod=c.gene_cod AND v.gene_ind=c.gene_ind
  AND ll.combtaf_id=v.combtaf_id);

 COMMIT;


select * from (
SELECT &outils.stragg(source) source,soc_cod, ets_cod, fournisseur_cod, combtaf_id, enreg_id, remise_tx, affaire_cod, tariflig_nolig, debvalid_dat, finvalid_dat, tariflig_qt, statut_typ, brut_mt, net_mt, enreg_s, reffour_cod, tariflot_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_ach_tariflig g --where exists (select 1 from &erp.ach_tariflig l where l.soc_cod=g.soc_cod and g.ets_cod=l.ets_cod and g.combtaf_id=l.combtaf_id and g.fournisseur_cod=l.fournisseur_cod and g.DEBVALID_DAT=l.DEBVALID_DAT)
  UNION ALL
  SELECT 'se' source ,g.*, null clergp FROM &erp.ach_tariflig g --where exists (select 1 from se_ach_tariflig l where l.soc_cod=g.soc_cod and g.ets_cod=l.ets_cod and g.combtaf_id=l.combtaf_id and g.fournisseur_cod=l.fournisseur_cod)
  )

GROUP BY  soc_cod, ets_cod, fournisseur_cod, combtaf_id, enreg_id, remise_tx, affaire_cod, tariflig_nolig, debvalid_dat, finvalid_dat, tariflig_qt, statut_typ, brut_mt, net_mt, enreg_s, reffour_cod, tariflot_ok
HAVING Count(1)!=2) r
ORDER BY soc_cod, ets_cod, fournisseur_cod,COMBTAF_ID,DEBVALID_DAT,TARIFLIG_QT,SOURCE ;


